﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.OracleClient;
using System.Data;


namespace CCPSIP
{
    public class OracleKitGroup
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };
        private const string kSystemMsgSender = "System";

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// 新增聊天群組，並產生新增群組訊息
        /// </summary>
        /// <param name="userInfo">群組主持人SipUserInfo物件</param>
        /// <param name="createTime">群組成立時間</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="groupName">群組名稱</param>
        /// <param name="members">群組成員帳號Array</param>
        /// <returns>Server端的GroupID</returns>
        public static int NewGroupChat(SipUserInfo userInfo, DateTime createTime, string clientGID, string groupName, string[] members)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                myTrans = connOracle.BeginTransaction();

                //【01】儲存聊天群組資訊
                //
                string insStr1 = "INSERT INTO group_chat"
                  + " (CreateTime, ClientGID, GroupName, Host)"
                  + " VALUES (:CreateTime, :ClientGID, :GroupName, :Host)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd1 = new OracleCommand(insStr1, connOracle, myTrans);
                insCmd1.Parameters.Add("CreateTime", OracleType.DateTime).Value = createTime;
                insCmd1.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
                insCmd1.Parameters.Add("GroupName", OracleType.VarChar).Value = groupName;
                insCmd1.Parameters.Add("Host", OracleType.VarChar).Value = userInfo.SIP_Num;
                insCmd1.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });
               
                insCmd1.ExecuteNonQuery();

                int GroupID = Convert.ToInt32(insCmd1.Parameters[":pid"].Value);

                //【02】儲存群組成員
                //
                foreach (string member in members)
                {
                    string insStr2 = "INSERT INTO group_member"
                      + " (GroupID, MemberNum, JoinTime, InvitedBy)"
                      + " VALUES (:GroupID, :MemberNum, :JoinTime, :InvitedBy)";
                    OracleCommand insCmd2 = new OracleCommand(insStr2, connOracle, myTrans);
                    insCmd2.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmd2.Parameters.Add("MemberNum", OracleType.VarChar).Value = member;
                    insCmd2.Parameters.Add("JoinTime", OracleType.DateTime).Value = createTime;
                    insCmd2.Parameters.Add("InvitedBy", OracleType.VarChar).Value = userInfo.SIP_Num;
                    insCmd2.ExecuteNonQuery();
                }

                //【03】儲存新增群組訊息
                //
                string insStrMsg = "INSERT INTO group_msg"
                  + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent)"
                  + " VALUES (:DateTime, '0', :Sender, :GroupID, 'GN', :MsgPreview, :MsgContent)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmdMsg = new OracleCommand(insStrMsg, connOracle, myTrans);
                insCmdMsg.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                insCmdMsg.Parameters.Add("Sender", OracleType.VarChar).Value = kSystemMsgSender;
                insCmdMsg.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                string notifyMsg = string.Format("{0} created the group \"{1}\"", userInfo.CustomName, groupName);
                insCmdMsg.Parameters.Add("MsgPreview", OracleType.VarChar).Value = notifyMsg;
                insCmdMsg.Parameters.Add("MsgContent", OracleType.VarChar).Value = notifyMsg;
                insCmdMsg.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });
                insCmdMsg.ExecuteNonQuery();

                long GMsgID = (long)Convert.ToInt32(insCmdMsg.Parameters[":pid"].Value);

                //【04】儲存群組訊息待讀取Table
                //
                foreach (string member in members)
                {
                    if (member == userInfo.SIP_Num)
                        continue;

                    string insStrRead = "INSERT INTO group_msg_read"
                      + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                    OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                    insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                    insCmdRead.Parameters.Add("Receiver", OracleType.VarChar).Value = member;
                    insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmdRead.ExecuteNonQuery();
                }

                //【99】Commit Transaction
                //
                myTrans.Commit();

                return GroupID;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查User是否為群組成員
        /// </summary>
        /// <param name="MemberNum">User帳號</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>是群組成員為true，否則false</returns>
        public static bool isMemberOfGroup(string MemberNum, string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT COUNT(*) FROM group_member m, group_chat c"
                  + " WHERE m.GroupID=c.pid AND c.ClientGID=:ClientGID"
                  + " AND m.MemberNum=:MemberNum AND m.DisableTime IS NULL";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
                selCmd.Parameters.Add("MemberNum", OracleType.VarChar).Value = MemberNum;
                int count = Convert.ToInt32(selCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查User是否曾經為群組成員
        /// </summary>
        /// <param name="MemberNum">User帳號</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>曾經是群組成員為true，否則false</returns>
        public static bool wasMemberOfGroup(string MemberNum, string clientGID)
        {
            if (string.IsNullOrEmpty(clientGID))
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                int GroupID = getGroupID(clientGID, connOracle);
                return wasMemberOfGroup(MemberNum, GroupID, connOracle);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查User是否曾經為群組成員
        /// </summary>
        /// <param name="MemberNum">User帳號</param>
        /// <param name="GroupID">Server端的群組ID</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>曾經是群組成員為true，否則false</returns>
        private static bool wasMemberOfGroup(string MemberNum, int GroupID, OracleConnection connOracle)
        {
            string selStr = "SELECT COUNT(*) FROM group_member"
              + " WHERE GroupID=:GroupID AND MemberNum=:MemberNum";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
            selCmd.Parameters.Add("MemberNum", OracleType.VarChar).Value = MemberNum;
            int count = Convert.ToInt32(selCmd.ExecuteScalar());
            return (count > 0);
        }


        /// <summary>
        /// 取得聊天群組成員資訊Array
        /// </summary>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>群組成員資訊Array</returns>
        public static ChatGroupMember[] getMembers(string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                return getMembers(clientGID, connOracle);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得聊天群組成員資訊Array (使用現有連線)
        /// </summary>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>群組成員資訊Array</returns>
        public static ChatGroupMember[] getMembers(string clientGID, OracleConnection connOracle)
        {
            //MySQL Code
            //    string selStr = "SELECT m.MemberNum, u.CustomName, u.DeviceType"
            //+ " FROM ccpsip.group_member m, ccpsip.group_chat c, ccpsip.reg_user u"
            //+ " WHERE m.GroupID=c.pid AND c.ClientGID=?ClientGID"
            //+ " AND m.DisableTime IS NULL AND m.MemberNum=u.SIP_Num AND u.Status='Y'"
            //+ " ORDER BY m.MemberNum<>c.Host, m.JoinTime, Length(m.MemberNum), m.MemberNum";

            //2014/??/?? CHChou 原汁原味改寫
            //【01】取得Host資料
            string selStr = "SELECT m.MemberNum, u.CustomName, u.DeviceType"
              + " FROM group_member m, group_chat c, reg_user u"
              + " WHERE m.GroupID=c.pid AND c.ClientGID=:ClientGID"
              + " AND m.DisableTime IS NULL AND m.MemberNum=u.SIP_Num AND u.Status='Y' AND m.MemberNum = c.Host"
              + " ORDER BY m.JoinTime, Length(m.MemberNum), m.MemberNum";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
            OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);

            //【02】取得其他Group Member資料
            selStr = "SELECT m.MemberNum, u.CustomName, u.DeviceType"
              + " FROM group_member m, group_chat c, reg_user u"
              + " WHERE m.GroupID=c.pid AND c.ClientGID=:ClientGID"
              + " AND m.DisableTime IS NULL AND m.MemberNum=u.SIP_Num AND u.Status='Y' AND m.MemberNum <> c.Host"
              + " ORDER BY m.JoinTime, Length(m.MemberNum), m.MemberNum";
            selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
            adapter = new OracleDataAdapter(selCmd);
            adapter.Fill(dataSet);

            int rowCount = dataSet.Tables[0].Rows.Count;
            ChatGroupMember[] memberArray = new ChatGroupMember[rowCount];

            for (int i = 0; i < rowCount; i++)
            {
                DataRow row = dataSet.Tables[0].Rows[i];
                string MemberNum = Convert.ToString(row["MemberNum"]);

                string iDeviceFamily = AppleKit.getIDeviceFamily(Convert.ToString(row["DeviceType"]));
                string DisplayName = string.Format("{0} {1}", row["CustomName"], iDeviceFamily);

                ChatGroupMember member = new ChatGroupMember(MemberNum, DisplayName, i == 0);
                memberArray[i] = member;
            }

            return memberArray;
        }


        /// <summary>
        /// 檢查User是否為群組主持人
        /// </summary>
        /// <param name="SIP_Num">User SIP帳號</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>是群組主持人為true，否則false</returns>
        public static bool isHostOfGroup(string SIP_Num, string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT COUNT(*) FROM group_chat"
                  + " WHERE ClientGID=:ClientGID AND Host=:Host";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
                selCmd.Parameters.Add("Host", OracleType.VarChar).Value = SIP_Num;
                int count = Convert.ToInt32(selCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得聊天群組名稱
        /// </summary>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>聊天群組名稱</returns>
        public static string getGroupName(string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT GroupName FROM group_chat"
                  + " WHERE ClientGID=:ClientGID";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得Server端的聊天群組ID
        /// </summary>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>Server端的聊天群組ID</returns>
        public static Int32 getGroupID(string clientGID, OracleConnection connOracle)
        {
            if (string.IsNullOrEmpty(clientGID))
                return -1;

            string selStr = "SELECT pid FROM group_chat"
              + " WHERE ClientGID=:ClientGID";
            OracleCommand selCmd = new OracleCommand(selStr, connOracle);
            selCmd.Parameters.Add("ClientGID", OracleType.VarChar).Value = clientGID;
            return Convert.ToInt32(selCmd.ExecuteScalar());
        }


        /// <summary>
        /// 取得Server端的聊天群組ID
        /// </summary>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>Server端的聊天群組ID</returns>
        public static Int32 getGroupID(string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                return getGroupID(clientGID, connOracle);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 離開聊天群組
        /// </summary>
        /// <param name="userInfo">執行者SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="leaver">離開的成員帳號</param>
        public static void LeaveGroup(SipUserInfo userInfo, string clientGID, string leaver)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int GroupID = getGroupID(clientGID, connOracle);

                ChatGroupMember[] newMembers = getMembers(clientGID, connOracle);

                //【97】Reset訊息提醒狀態
                //
                OracleKitMsg.SetMsgNotify(leaver, clientGID, true);

                myTrans = connOracle.BeginTransaction();


                //【01】停用群組成員
                //
                string updStr1 = "UPDATE group_member"
                  + " SET DisableTime=:DateTime, DisabledBy=:DisabledBy"
                  + " WHERE GroupID=:GroupID AND MemberNum=:MemberNum";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd1.Parameters.Add("DisabledBy", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                updCmd1.Parameters.Add("MemberNum", OracleType.VarChar).Value = leaver;
                updCmd1.ExecuteNonQuery();


                //【02】清除未讀訊息
                //
                string delStr2 = "DELETE FROM group_msg_read"
                  + " WHERE GroupID=:GroupID AND Receiver=:Receiver AND ReadTime IS NULL";
                OracleCommand delCmd2 = new OracleCommand(delStr2, connOracle, myTrans);
                delCmd2.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                delCmd2.Parameters.Add("Receiver", OracleType.VarChar).Value = leaver;
                delCmd2.ExecuteNonQuery();


                //【03】放棄取得已讀標記
                //
                string updStr3 = "UPDATE group_msg SET Status='L'"
                  + " WHERE GroupID=:GroupID AND Sender=:Sender AND Status IN ('0','2')";
                OracleCommand updCmd3 = new OracleCommand(updStr3, connOracle, myTrans);
                updCmd3.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                updCmd3.Parameters.Add("Sender", OracleType.VarChar).Value = leaver;
                updCmd3.ExecuteNonQuery();

                if (newMembers.Length > 0)
                {
                    //【04-1】儲存離開群組訊息
                    //
                    string insStrMsg = "INSERT INTO group_msg"
                      + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent, UpdateObj)"
                      + " VALUES (:DateTime, '0', :Sender, :GroupID, 'GL', :MsgPreview, :MsgContent, :UpdateObj)"
                      + " RETURNING pid INTO :pid";
                    OracleCommand insCmdMsg = new OracleCommand(insStrMsg, connOracle, myTrans);
                    insCmdMsg.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    insCmdMsg.Parameters.Add("Sender", OracleType.VarChar).Value = kSystemMsgSender;
                    insCmdMsg.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;

                    string leaverName = SipKit.getDispNameShort(leaver);
                    string notifyMsg = string.Format("{0} left", leaverName);
                    if (userInfo.SIP_Num != leaver)
                        notifyMsg = string.Format("{0} was removed from the group", leaverName);
                    insCmdMsg.Parameters.Add("MsgPreview", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("MsgContent", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("UpdateObj", OracleType.VarChar).Value = leaver;
                    insCmdMsg.Parameters.Add(new OracleParameter
                    {
                        ParameterName = ":pid",
                        OracleType = OracleType.Number,
                        Direction = ParameterDirection.Output
                    });
                    insCmdMsg.ExecuteNonQuery();

                    long GMsgID = (long)Convert.ToInt32(insCmdMsg.Parameters[":pid"].Value);


                    //【04-2】儲存群組訊息待讀取Table
                    //
                    string insStrRead = "INSERT INTO group_msg_read"
                      + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                    OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                    insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                    insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmdRead.Parameters.Add("Receiver", OracleType.VarChar);

                    foreach (ChatGroupMember member in newMembers)
                    {
                        if (member.MemberID == userInfo.SIP_Num)
                        {
                            continue;//本人免通知
                        }
                        insCmdRead.Parameters["Receiver"].Value = member.MemberID;
                        insCmdRead.ExecuteNonQuery();
                    }

                    //【04-3】非自行離開須通知
                    //
                    if (userInfo.SIP_Num != leaver)
                    {
                        insCmdRead.Parameters["Receiver"].Value = leaver;
                        insCmdRead.ExecuteNonQuery();
                    }
                }

                if (leaver == userInfo.SIP_Num && isHostOfGroup(userInfo.SIP_Num, clientGID))
                {
                    //【98】Host離開：更換群組Host
                    //
                    string updStr7 = "UPDATE group_chat SET Host=:Host WHERE pid=:GroupID";
                    OracleCommand updCmd7 = new OracleCommand(updStr7, connOracle, myTrans);
                    string newHost = (newMembers.Length > 0 ? newMembers[0].MemberID : null);
                    updCmd7.Parameters.Add("Host", OracleType.VarChar).Value = newHost;
                    updCmd7.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    updCmd7.ExecuteNonQuery();
                }


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 加入聊天群組
        /// </summary>
        /// <param name="userInfo">邀請者SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="newMembers">邀請的新成員帳號Array</param>
        public static void JoinGroup(SipUserInfo userInfo, string clientGID, string[] newMembers)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int GroupID = getGroupID(clientGID, connOracle);
                int memberCount = 0;
                DateTime reqTime = DateTime.Now;

                //【03】儲存訊息讀取狀態Table
                //
                ChatGroupMember[] latestMembers = getMembers(clientGID, connOracle);

                List<Boolean> WasMemberOfGroup = new List<Boolean>();

                foreach (string newMember in newMembers)
                {
                    if (wasMemberOfGroup(newMember, GroupID, connOracle))
                    {
                        WasMemberOfGroup.Add(true);
                    }
                    else
                    {
                        WasMemberOfGroup.Add(false);
                    }
                }

                myTrans = connOracle.BeginTransaction();

                List<long> GMsgIDs = new List<long>();

                foreach (string newMember in newMembers)
                {
                    //【01】儲存新群組成員
                    //
                    if (WasMemberOfGroup.ElementAt(memberCount))
                    {
                        string updStr1 = "UPDATE group_member SET"
                          + " JoinTime=:DateTime, InvitedBy=:InvitedBy, DisableTime=NULL, DisabledBy=NULL"
                          + " WHERE GroupID=:GroupID AND MemberNum=:MemberNum";
                        OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                        updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                        updCmd1.Parameters.Add("InvitedBy", OracleType.VarChar).Value = userInfo.SIP_Num;
                        updCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                        updCmd1.Parameters.Add("MemberNum", OracleType.VarChar).Value = newMember;
                        updCmd1.ExecuteNonQuery();
                    }
                    else
                    {
                        string insStr1 = "INSERT INTO group_member"
                          + " (GroupID, MemberNum, JoinTime, InvitedBy) VALUES"
                          + " (:GroupID, :MemberNum, :DateTime, :InvitedBy)";
                        OracleCommand insCmd1 = new OracleCommand(insStr1, connOracle, myTrans);
                        insCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                        insCmd1.Parameters.Add("MemberNum", OracleType.VarChar).Value = newMember;
                        insCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                        insCmd1.Parameters.Add("InvitedBy", OracleType.VarChar).Value = userInfo.SIP_Num;
                        insCmd1.ExecuteNonQuery();
                    }


                    //【02】儲存加入群組訊息
                    //
                    string insStrMsg = "INSERT INTO group_msg"
                      + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent, UpdateObj)"
                      + " VALUES (:DateTime, '0', :Sender, :GroupID, 'GJ', :MsgPreview, :MsgContent, :UpdateObj)"
                      + " RETURNING pid INTO :pid";
                    OracleCommand insCmdMsg = new OracleCommand(insStrMsg, connOracle, myTrans);
                    insCmdMsg.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                    insCmdMsg.Parameters.Add("Sender", OracleType.VarChar).Value = kSystemMsgSender;
                    insCmdMsg.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    string notifyMsg = string.Format("{0} joined", SipKit.getDispNameShort(newMember));
                    insCmdMsg.Parameters.Add("MsgPreview", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("MsgContent", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("UpdateObj", OracleType.VarChar).Value = newMember;
                    insCmdMsg.Parameters.Add(new OracleParameter
                    {
                        ParameterName = ":pid",
                        OracleType = OracleType.Number,
                        Direction = ParameterDirection.Output
                    });
                    insCmdMsg.ExecuteNonQuery();
                    memberCount++;
                    GMsgIDs.Add((long)Convert.ToInt32(insCmdMsg.Parameters[":pid"].Value));
                }

                foreach (long GMsgID in GMsgIDs)
                {
                    foreach (ChatGroupMember member in latestMembers)
                    {
                        if (member.MemberID == userInfo.SIP_Num)
                            continue;

                        string insStrRead = "INSERT INTO group_msg_read"
                          + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                        OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                        insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                        insCmdRead.Parameters.Add("Receiver", OracleType.VarChar).Value = member.MemberID;
                        insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                        insCmdRead.ExecuteNonQuery();
                    }
                }

                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得User目前參與的聊天群組
        /// </summary>
        /// <param name="SIP_Num">User SIP帳號</param>
        /// <returns>聊天群組Dictionary Array</returns>
        public static Dictionary<string, string>[] getUserGroups(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT g.ClientGID, g.GroupName, g.IconTime, g.IconSize"
                  + " FROM group_member mb, group_chat g, group_msg mg"
                  + " WHERE mb.MemberNum=:MemberNum AND mb.DisableTime IS NULL"
                  + "   AND mb.GroupID=g.pid AND g.pid=mg.GroupID"
                  + " GROUP BY g.ClientGID, g.GroupName, g.IconTime, g.IconSize";

                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("MemberNum", OracleType.VarChar).Value = SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                int rowCount = dataSet.Tables[0].Rows.Count;
                Dictionary<string, string>[] dictArray = new Dictionary<string, string>[rowCount];

                for (int i = 0; i < rowCount; i++)
                {
                    DataRow row = dataSet.Tables[0].Rows[i];
                    Dictionary<string, string> groupDict = new Dictionary<string, string>();
                    groupDict["GroupID"] = Convert.ToString(row["ClientGID"]);
                    groupDict["GroupName"] = Convert.ToString(row["GroupName"]);

                    if (row["IconTime"] != DBNull.Value)
                    {
                        groupDict["IconTime"] = ChrisKit.toUTCTimeStr(row["IconTime"]);
                        groupDict["IconSize"] = Convert.ToString(row["IconSize"]);
                    }
                    else
                    {
                        groupDict["IconTime"] = "";
                        groupDict["IconSize"] = "";
                    }
                    dictArray[i] = groupDict;
                }

                return dictArray;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 重新命名聊天群組
        /// </summary>
        /// <param name="SIP_Num">User SIP帳號</param>
        /// <param name="ClientGID">Client端的群組ID</param>
        /// <param name="NewName">新的群組名稱</param>
        public static void RenameGroup(string SIP_Num, string ClientGID, string NewName)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int GroupID = getGroupID(ClientGID, connOracle);

                //【02】通知群組重新命名
                //
                ChatGroupMember[] gMembers = getMembers(ClientGID, connOracle);

                myTrans = connOracle.BeginTransaction();


                //【01】重新命名群組
                //
                string updStr1 = "UPDATE group_chat SET GroupName=:GroupName"
                  + " WHERE pid=:GroupID";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("GroupName", OracleType.VarChar).Value = NewName;
                updCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                updCmd1.ExecuteNonQuery();

                if (gMembers.Length > 1)
                {
                    //【02-1】儲存重新命名訊息
                    //
                    string insStrMsg = "INSERT INTO group_msg"
                      + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent, UpdateObj) VALUES"
                      + " (:DateTime, '0', :Sender, :GroupID, 'GR', :MsgPreview, :MsgContent, :UpdateObj)"
                      + " RETURNING pid INTO :pid";
                    OracleCommand insCmdMsg = new OracleCommand(insStrMsg, connOracle, myTrans);
                    insCmdMsg.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    insCmdMsg.Parameters.Add("Sender", OracleType.VarChar).Value = kSystemMsgSender;
                    insCmdMsg.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    string notifyMsg = string.Format("The group was renamed to \n\"{0}\"", NewName);
                    insCmdMsg.Parameters.Add("MsgPreview", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("MsgContent", OracleType.VarChar).Value = notifyMsg;
                    insCmdMsg.Parameters.Add("UpdateObj", OracleType.VarChar).Value = SIP_Num;
                    insCmdMsg.Parameters.Add(new OracleParameter
                    {
                        ParameterName = ":pid",
                        OracleType = OracleType.Number,
                        Direction = ParameterDirection.Output
                    });
                    insCmdMsg.ExecuteNonQuery();

                    long GMsgID = (long)Convert.ToInt32(insCmdMsg.Parameters[":pid"].Value);


                    //【02-2】儲存群組訊息待讀取Table
                    //
                    string insStrRead = "INSERT INTO group_msg_read"
                      + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                    OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                    insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                    insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmdRead.Parameters.Add("Receiver", OracleType.VarChar);

                    foreach (ChatGroupMember member in gMembers)
                    {
                        if (member.MemberID == SIP_Num)
                        {
                            continue;//本人免通知
                        }
                        insCmdRead.Parameters["Receiver"].Value = member.MemberID;
                        insCmdRead.ExecuteNonQuery();
                    }
                }


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 記錄群組圖片更新時間
        /// </summary>
        /// <param name="GroupID">Server端的群組ID</param>
        /// <param name="reqTime">圖片更新時間</param>
        /// <param name="iconSize">圖片檔案大小</param>
        public static void UpdateIconTime(int GroupID, DateTime reqTime, int iconSize)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr1 = "UPDATE group_chat"
                  + " SET IconTime=:IconTime, IconSize=:IconSize"
                  + " WHERE pid=:GroupID";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                updCmd1.Parameters.Add("IconTime", OracleType.DateTime).Value = reqTime;
                updCmd1.Parameters.Add("IconSize", OracleType.Int32).Value = iconSize;
                updCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                updCmd1.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }

        ///<summary>檢查群組是否存在</summary>
        ///
        public static bool IsGroupExist(string GID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            string cmd_all = "SELECT PID"
                            + " FROM GROUP_CHAT"
                            + " WHERE CLIENTGID = :CLIENTGID";
            
            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            cmd.Parameters.Add(":CLIENTGID", OracleType.VarChar).Value = GID;
            connOracle.Open();

            if (String.IsNullOrEmpty(Convert.ToString(cmd.ExecuteScalar())))
            {
                connOracle.Dispose();
                return false;
            }
            else
            {
                connOracle.Dispose();
                return true;
            }
        }


    }


    /// <summary>
    /// 群組成員資訊
    /// </summary>
    public class ChatGroupMember
    {
        private string _MemberID;
        private string _DisplayName;
        private string _IsHost;

        public string MemberID { get { return this._MemberID; } }
        public string DisplayName { get { return this._DisplayName; } }
        public string IsHost { get { return this._IsHost; } }

        public ChatGroupMember(string memberID, string displayName, bool isHost)
        {
            _MemberID = memberID;
            _DisplayName = displayName;
            _IsHost = isHost ? "Y" : "N";
        }
    }
}